import pymysql
import pandas as pd
from Database import DatabaseHandler

db_handler = DatabaseHandler(host='localhost', port=3306, user='root', password='123456')
db_handler.create_database('test')
db_handler.cursor.execute('USE test')

# 创建表
sql = """    
create table if not exists school_admission_plan(    
    school varchar(225),  	  
    academic_system varchar(225),  
    batch varchar(225),	  	  
    enrollment_plan varchar(225),  
    subject_selection_requirements varchar(225),  
    professional_group varchar(225),	  
    professional_name mediumtext,	  
    tuition varchar(225),
    category varchar(225),
    area varchar(225)
)    
"""
db_handler.cursor.execute(sql)
print('表创建或已存在，SQL执行成功')

# 读取Excel文件
df = pd.read_excel(r"C:\Users\姚宇康\Desktop\日常文件\学习\2024各校招生计划.xlsx")
df.fillna('', inplace=True)  # 填充空值

# 确保DataFrame的列名与数据库表的列名相匹配
# 如果不匹配，您可能需要重命名DataFrame的列
# 例如：df.rename(columns={'原列名': '新列名'}, inplace=True)

# 插入数据的SQL语句，确保占位符数量与DataFrame的列数相匹配
sql_insert = 'INSERT INTO school_admission_plan VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,%s)'
try:
    db_handler.cursor.executemany(sql_insert, df.to_records(index=False).tolist())
    print(db_handler.cursor.rowcount, "记录插入成功。")
except pymysql.MySQLError as e:
    print(f"数据库错误：{e}")
    # 注意：由于autocommit=True，通常不需要手动回滚

db_handler.close_connection()